package com.yalu.read.excel.purchase;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.yalu.entity.PurchaseData;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class PurchaseThreeExcelRead {

    /**
     * 测试pdf文件的创建
     *
     * @param args
     */
    public static void main(String[] args) {
        String filePath = "D://yalu//purchase//";  //这里先手动把绝对路径的文件夹给补上。
        List<String> filePaths = new ArrayList<>();
        recursionFile(filePath, filePaths);
        List<PurchaseData> list = new ArrayList<>();
        for (String fileName : filePaths) {
            PurchaseThreeExcelRead.readExcel(fileName, list);
        }
        System.out.println("文件数量：" + filePaths.size() + " 数据数量: " + list.size());
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        String outFileName = "D:\\yalu\\采购单_" + LocalDateTime.now().format(dtf) + ".xlsx";
        writerFils(list, outFileName);
    }

    /**
     * 读PDF文件，使用了pdfbox开源项目
     *
     * @param fileName
     */
    public static void readExcel(String fileName, List<PurchaseData> list) {
        File file = new File(fileName);
        FileInputStream in = null;
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(fileName);
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            int sheetNum = workbook.getNumberOfSheets();
            for (int y = 0; y <= sheetNum; y++) {
                List<HashMap<Integer, String>> hashMaps = EasyExcel.read(fileName).sheet(y).doReadSync();
                int size = hashMaps.size();
                Queue<PurchaseData> dataQueue = new LinkedList<>();
                for (int i = 0; i < size; i++) {
                    PurchaseData dataNow = dataQueue.poll();
                    PurchaseData purchaseData = new PurchaseData();
                    if (null != dataNow) {
                        BeanUtils.copyProperties(dataNow, purchaseData);
                    }
                    HashMap<Integer, String> lineMaps = hashMaps.get(i);
                    String lineText = JSON.toJSONString(hashMaps.get(i));
                    if (StringUtils.hasText(lineText) && (lineText.toLowerCase().contains("订单编号："))) {
                        purchaseData.setPurchaseNo(lineMaps.get(lineMaps.size() - 1).trim());
//                        purchaseData.setPurchaseNo(lineMaps.get(11).trim());
                    } else if (StringUtils.hasText(lineText) && (lineText.toLowerCase().contains("订单日期："))) {
                        purchaseData.setDate(lineMaps.get(lineMaps.size() - 1).trim());
//                        purchaseData.setDate(lineMaps.get(11).trim());
                    } else if (StringUtils.hasText(lineText) && (lineText.toLowerCase().contains("供方：")||lineText.contains("供应商："))) {
                        String content = String.valueOf(lineMaps.get(8));
                        if (StringUtils.hasText(content) && !"null".equals(content)) {
                            purchaseData.setDate(content.trim());
                        } else {
                            purchaseData.setDate(lineMaps.get(10).trim());
                        }
                    } else if (StringUtils.hasText(lineText) && (lineText.toLowerCase().contains("总 计："))) {
                        String content = String.valueOf(lineMaps.get(8));
                        if (StringUtils.hasText(content) && !"null".equals(content)) {
                            purchaseData.setDate(content.trim());
                        } else {
                            purchaseData.setDate(lineMaps.get(10).trim());
                        }
                    }  else if (StringUtils.hasText(lineText) && (lineText.toLowerCase().contains("二：订购条款"))) {
                        HashMap<Integer, String> amountLineMaps = hashMaps.get(i - 1);
                        String content = String.valueOf(amountLineMaps.get(5));
                        if (StringUtils.hasText(content) && !"null".equals(content)) {
                            purchaseData.setTotal(amountLineMaps.get(5).trim());
                            purchaseData.setAmount(amountLineMaps.get(6).trim());
                        } else {
                            String content1 = String.valueOf(amountLineMaps.get(6));
                            if (StringUtils.hasText(content1) && !"null".equals(content1)) {
                                purchaseData.setTotal(amountLineMaps.get(6).trim());
                                purchaseData.setAmount(amountLineMaps.get(7).trim());
                            } else {
                                purchaseData.setTotal(amountLineMaps.get(7).trim());
                                purchaseData.setAmount(amountLineMaps.get(8).trim());
                            }

                        }
                    }
                    dataQueue.offer(purchaseData);
                    if (StringUtils.hasText(purchaseData.getAmount()) && StringUtils.hasText(purchaseData.getPurchaseNo()) && StringUtils.hasText(purchaseData.getDate())) {
                        PurchaseData data = dataQueue.poll();
                        data.setFileName(fileName);
                        list.add(data);
                        break;
                    }
                }
            }

        } catch (Exception e) {
            System.out.println("读取PDF文件" + file.getAbsolutePath() + "生失败！" + e);
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e1) {
                }
            }
        }
    }

    private static boolean isNull(String str) {
        boolean isNull = false;
        if (StringUtils.hasText(str)) {
            if (!"null".equals(str) && !"[null".equals(str) && !"null]".equals(str)) {
                isNull = true;
            }
        }
        return isNull;
    }

    /**
     * 写入文件
     *
     * @param data
     * @param outFileName
     */
    public static void writerFils(List<PurchaseData> data, String outFileName) {
        try {
            ExcelWriter excelWriter = EasyExcel.write(outFileName, PurchaseData.class).build();
            String sheetName = "1";
            List<PurchaseData> list = new ArrayList<>(data.size());
            for (PurchaseData purchaseData : data) {
                PurchaseData billDetailDataExcel = new PurchaseData();
                BeanUtils.copyProperties(purchaseData, billDetailDataExcel);
                list.add(billDetailDataExcel);
            }
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(list, writeSheet);

            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            System.out.println("文件写入失败：" + e.getMessage());
        }
    }

    /**
     * 递归文件
     *
     * @param path
     * @param filePaths
     */
    public static void recursionFile(String path, List<String> filePaths) {
        if (StringUtils.hasText(path)) {
            File file = new File(path);
            for (File f : file.listFiles()) {
                if (f.isDirectory()) {
                    recursionFile(f.getPath(), filePaths);
                } else {
                    if (f.getPath().toLowerCase().contains(".xls") || f.getPath().toLowerCase().contains(".xlsx")) {
                        filePaths.add(f.getPath());
                    }
                }
            }
        }
    }
}
